import java.sql.*;
import java.util.Scanner;

public class Main {
    private static void m1(){
        //查询所有用户
        Connection connection=null;
        PreparedStatement preparedStatement =null;
        ResultSet resultset =null;



        try {
            //加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //创建连接
            String url = "jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf-8";
            connection = DriverManager.getConnection(url, "root", "1234");
            if(connection!=null){
                System.out.println("连接成功");
            }

            //预处理
            String sql = "select * from user ";
            preparedStatement = connection.prepareStatement(sql);


            //执行
            resultset = preparedStatement.executeQuery();

            //展示结果
            while(resultset.next()){
                int id=resultset.getInt(1);
                String name =resultset.getString(2);
                String pass =resultset.getString(3);
                System.out.println(id+"_______"+name+"______"+pass);
            }





        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                if (resultset!=null)
                    resultset.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            try {
                if(preparedStatement!=null)
                    preparedStatement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            try {
                if(connection!=null)
                    connection.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }



    }

    private static void m2(){

        Scanner sc = new Scanner(System.in);
        System.out.println("请输入要查询的用户名：");
        String selected = sc.nextLine();



        Connection conn = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            //2、获取连接
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf-8","root","1234");

            //3.sql语句
            String sql = "select * from user where name like ?";

            //4.预编译sql
            preparedStatement = conn.prepareStatement(sql);


            preparedStatement.setString(1,"%"+selected+"%");

            //5.执行sql
            resultSet = preparedStatement.executeQuery();
            //6.遍历user
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String password = resultSet.getString("pass");
                System.out.println(id + " " + name + " " + password);
            }
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try{
                //7.关闭资源
                if (resultSet != null) {
                    resultSet.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            }catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }


    private static void m3() {
        Scanner sc = new Scanner(System.in);

        Connection conn = null;
        PreparedStatement preparedStatement = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            // 获取连接
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf-8", "root", "1234");

            // SQL语句
            System.out.println("请输入要添加的用户信息：");
            System.out.println("请输入用户名：");
            String name = sc.nextLine();
            System.out.println("请输入密码：");
            String pass = sc.nextLine();

            String sql = "INSERT INTO user (name, pass) VALUES (?, ?)";

            // 预编译SQL
            preparedStatement = conn.prepareStatement(sql);

            preparedStatement.setString(1, name);
            preparedStatement.setString(2, pass);

            // 执行SQL
            int affectedRows = preparedStatement.executeUpdate();

            // 判断是否添加成功
            if (affectedRows > 0) {
                System.out.println("添加成功！");
            } else {
                System.out.println("添加失败！");
            }

        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭资源
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }


    public static void m4(){

            Scanner sc = new Scanner(System.in);

            Connection conn = null;
            PreparedStatement preparedStatement = null;
            try {
                Class.forName("com.mysql.cj.jdbc.Driver");

                // 获取连接
                conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf-8", "root", "1234");

                // SQL语句
                System.out.println("请输入要修改的用户信息：");
                System.out.println("请输入用户名：");
                String name = sc.nextLine();
                System.out.println("请输入密码：");
                String pass = sc.nextLine();
                System.out.println("请输入用户id：");
                int id = sc.nextInt();

                String sql = "UPDATE user SET name = ?, pass = ? WHERE id = ? ";

                // 预编译SQL
                preparedStatement = conn.prepareStatement(sql);

                preparedStatement.setString(1, name);
                preparedStatement.setString(2, pass);
                preparedStatement.setInt(3, id);

                // 执行SQL
                int affectedRows = preparedStatement.executeUpdate();

                // 判断是否添加成功
                if (affectedRows > 0) {
                    System.out.println("修改成功！");
                } else {
                    System.out.println("修改失败！");
                }

            } catch (ClassNotFoundException | SQLException e) {
                throw new RuntimeException(e);
            } finally {
                // 关闭资源
                try {
                    if (preparedStatement != null) {
                        preparedStatement.close();
                    }
                    if (conn != null) {
                        conn.close();
                    }
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
    }



    public static void main(String[] args) {
        System.out.println("请输入要查询的序号："+"\n"+"1.查询所有用户"+"\n"+"2.根据用户名查询用户"+"\n"+"3.添加用户"+"\n"+"4.修改用户信息");
        Scanner sc =new Scanner(System.in);
        String selected = sc.nextLine();

        if (selected.equals("1")){
            m1();
        }else if(selected.equals( "2")){
            m2();
        }else if (selected.equals("3")){
            m3();
        } else if (selected.equals("4")) {
            m4();
        }


    }
}


